package com.qiaolang.dao;

import com.qiaolang.db.DB;
import com.qiaolang.pojo.Card;
import com.qiaolang.pojo.Users;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.text.SimpleDateFormat;
import java.util.List;


/**
 * Created by lenovo on 2017/11/12.
 */
@Repository
public class CardDAO {
    public Connection conn = null;
    public CardDAO() {
        conn = DB.getConnection();
    }
    public boolean register(Card card) {//注册
        PreparedStatement pstmt=null;
        boolean flag=false;
        boolean flag1=false;
        String sql="insert into card values(?,?,?,?,?,?,?,?)";
        String sql1="insert into users values(?,?,?,?)";
        try {
            //1.先增加主表后增加从表
            pstmt=conn.prepareStatement(sql1);
            pstmt.setString(1,card.getUserno());
            pstmt.setString(2,card.getUsername());
            pstmt.setString(3,"student");
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");//设置日期格式
            System.out.println(df.format(new Date()).toString());// new Date()为获取当前系统时间
            String datetime=df.format(new Date()).toString();
            pstmt.setString(4,datetime);
            flag1=pstmt.executeUpdate()>0?true:false;

            //增加从表
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,card.getUserno());
            pstmt.setString(2,card.getUsername());
            pstmt.setString(3,card.getSex());
            pstmt.setString(4,card.getCollege());
            pstmt.setString(5,card.getClasses());
            pstmt.setString(6,card.getBalance());
            pstmt.setString(7,datetime);
            pstmt.setString(8,card.getState());

            flag=pstmt.executeUpdate()>0?true:false;



            System.out.println(flag);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(pstmt!=null){
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return flag&&flag1;
    }
    public Card queryById(String userno){
        PreparedStatement pstmt;
        ResultSet rs;
        Card card=new Card();
        try {
            pstmt = conn.prepareStatement("select * from card where userno=?");
            pstmt.setString(1,userno);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                card.setUserno(rs.getString(1));
                card.setUsername(rs.getString(2));
                card.setSex(rs.getString(3));
                card.setCollege(rs.getString(4));
                card.setClasses(rs.getString(5));
                card.setBalance(rs.getString(6));
                card.setDatetime(rs.getString(7));
                card.setState(rs.getString(8));
            }
            rs.close();
            pstmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return card;
    }

    //删除的是两个表
    public boolean del(String userno){
        PreparedStatement pstmt=null;
        boolean flag=false;
        boolean flag1=false;
        String sql="delete from users where userno=?";
        String sql1="delete from card where userno=?";
        try {
            //1.先删除从表
            pstmt=conn.prepareStatement(sql1);
            pstmt.setString(1,userno);
            flag1=pstmt.executeUpdate()>0?true:false;

            //再删除主表
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,userno);
            flag=pstmt.executeUpdate()>0?true:false;
            System.out.println(flag);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(pstmt!=null){
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return flag&&flag1;
    }
    public boolean upt(String userno,String role){
        PreparedStatement pstmt=null;
        boolean flag=false;
        String sql="update users set role=? where userno=?";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,role);
            pstmt.setString(2,userno);
            flag=pstmt.executeUpdate()>0?true:false;
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(pstmt!=null){
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return flag;
    }
    public boolean uptStatus(String userno){//传0或者1表示 挂失和补卡
        //补卡的时间要不要改一下  ????(但是最后考虑到补卡的时候日期也应该改变所以用两条sql)
        PreparedStatement pstmt=null;
        boolean flag=false;
        String sql="update card set  STATE=0 where userno=?";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,userno);
            flag=pstmt.executeUpdate()>0?true:false;
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(pstmt!=null){
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return flag;
    }
    public boolean buKa(String userno){
        PreparedStatement pstmt=null;
        boolean flag=false;
        String sql="update card set  STATE=1,datetime=? where userno=?";
        try {
            pstmt=conn.prepareStatement(sql);
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");//设置日期格式
            String datetime=df.format(new Date()).toString();
            pstmt.setString(1,datetime);
            pstmt.setString(2,userno);
            flag=pstmt.executeUpdate()>0?true:false;
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(pstmt!=null){
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return flag;
    }
    public boolean chongZhi(String userno,int balance){
        PreparedStatement pstmt=null;
        boolean flag=false;
        String sql="update card set  balance=? where userno=?";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,balance);
            pstmt.setString(2,userno);
            flag=pstmt.executeUpdate()>0?true:false;
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(pstmt!=null){
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return flag;
    }

    public String getBalance(String userno){
        PreparedStatement pstmt=null;
        ResultSet rs;
        String sql="select balance from card where userno=?";
        String balance="";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,userno);
            rs = pstmt.executeQuery();
            while (rs.next()){
                balance=rs.getString("balance");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(pstmt!=null){
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return balance;
    }
}
